1

Import the ESRI shapefile of German districts and the district attribute table. Join the two data frames, transform the CRS to “EPSG:3035” and check your changes.

You need to rename one of the id variables or adjust your join accordingly (“id = district_id”).

# Import data
german_districts <- st_read(dsn = "./data",
                           layer = "GER_DISTRICTS") %>% 
                     rename(., district_id = id) #

attributes_districts <-  read.csv("./data/attributes_districts.csv", 
                                  header = T, fill = T, sep = ",") 


# Join data and transform
german_districts_enhanced <- 
  german_districts %>% 
  left_join(., attributes_districts, by = "district_id") %>% 
  st_transform(., crs = 3035)

# Check
st_crs(german_districts_enhanced)
head(german_districts_enhanced, 2)

2

We want a first descriptive visual of the distribution of Covid-19 cases in Cologne and the surrounding districts. Calculate the number of Covid-19 cases in the last 7 days (cases_7days) by population (population) and multiply with 100,000 (in Germany usually called "7 Tages Inzidenzzahl).

Select Cologne (district_id == 5315), find the surrounding districts and plot the Covid Rate in Cologne and the surrounding districts.

You can use the dplyr function bind_rows to combine the two spatial objects “Cologne” and “Cologne Surroundings”.

# calculate Covid-19 rate
german_districts_enhanced <-
  german_districts_enhanced %>% 
  mutate(covid7d_rate = (cases_7days / population) * 100000)

# filter Cologne
cologne <-
  german_districts_enhanced %>% 
  filter(. , district_id == 5315)

# filter surrounding districts, append with Cologne data and select the Covid column
cologne_sur <-
  german_districts_enhanced %>%
  filter(lengths(st_touches(., cologne)) > 0) %>% 
  bind_rows(., cologne) %>%   
  select(. , covid7d_rate)

# plot  
plot(cologne_sur)

3

Save your data set of Cologne and its surrounding districts as a ESRI Shapefile.

# Export as Shapefile
st_write(cologne_sur, 
         dsn = "./data/own_materials/cologne_covid19_eps3035", 
         delete_layer = TRUE) #optional